package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.Clazz;
import cn.ztydata.teachingsystem.heaven.entity.Teacher;
import com.sun.xml.internal.bind.v2.runtime.reflect.Lister;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 班级Dao
 * Created by fanruiping on 2015/5/5.
 */
@Repository
public class ClassDao extends BaseDao {
    /**
     * 根据班级代码获取班级明细
     * @param number 班级代码
     * @return Clazz
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public Clazz getByNumber(String number){
        try{
            String sql = "SELECT id,sName,sNumber,sDeptNum,sSpecNum,sTeacherNum FROM t_class WHERE sNumber=:number";

            log.debug("返回根据班级代码获取的班级明细数据");

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("number", number), new RowMapper<Clazz>() {
                @Override
                public Clazz mapRow(ResultSet resultSet, int i) throws SQLException {
                    Clazz clazz = new Clazz();

                    clazz.setId(resultSet.getLong("id"));
                    clazz.setName(resultSet.getString("sName"));
                    clazz.setNumber(resultSet.getString("sNumber"));
                    clazz.setDeptNum(resultSet.getString("sDeptNum"));
                    clazz.setSpecNum(resultSet.getString("sSpecNum"));
                    clazz.setTeacherNum(resultSet.getString("sTeacherNum"));

                    return clazz;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("根据班级代码未获取到班级数据:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * 根据班级代码Name集合获取班级名称集合
     * @param numbers 班级代码集合
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public Map<String,String> getNameByNumbers(List<String> numbers){
        final Map<String,String> result = new HashMap<>();

            String sql = "SELECT id,sName,sNumber FROM t_class WHERE sNumber IN(:numbers)";

            namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("numbers", numbers), new RowMapper<Object>() {
                @Override
                public Object mapRow(ResultSet rs, int i) throws SQLException {
                    result.put(rs.getString("sNumber"),rs.getString("sName"));

                    return null;
                }
            });

        return result;
    }

    /**
     * 根据班级编号集合获取班级名称集合
     * @param ids 班级代码集合
     * @return Map
     *
     * @author fanruiping
     * @since 2015-05-06
     */
    public Map<Long,String> getNameByIds(Set<Long> ids){
        final Map<Long,String> result = new HashMap<>();

        String sql = "SELECT id,sName FROM t_class WHERE id IN(:ids)";

        namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource().addValue("ids", ids), new RowMapper<Object>() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                result.put(rs.getLong("id"),rs.getString("sName"));

                return null;
            }
        });

        return result;
    }

    /**
     * 获取与班级主表中重复的班级代码
     * @param classNums 班级代码集合
     * @return String
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public String getDuplicateNum(final Set<String> classNums) {
        if (classNums.isEmpty()) {
            log.debug("班级代码集合为空");

            return null;
        }

        try {
            String sql = "SELECT sNumber FROM t_class WHERE sNumber IN (:numSet) LIMIT 1";

            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", classNums);

            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("数据库中无重复的班级代码");

            return null;
        }
    }

    /**
     * 获取与归档表中重复的班级代码
     * @param classNums 班级代码集合
     * @return String
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public String getDuplicateNumInHistory(final Set<String> classNums) {
        if (classNums.isEmpty()) {
            log.debug("班级代码集合为空");

            return null;
        }

        try {
            String sql = "SELECT sNumber FROM t_hclass WHERE sNumber IN (:numSet) LIMIT 1";

            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", classNums);

            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("数据库中无重复的班级代码");

            return null;
        }
    }

    /**
     * 根据班级编号获取班级信息
     * @param id 班级编号
     * @return Clazz
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public Clazz getById(Long id){
        try{
            String sql = "SELECT id,sName,sNumber,sDeptNum,sSpecNum,sTeacherNum FROM t_class WHERE id=:id";

            log.debug("返回指定班级数据");

            return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource().addValue("id", id), new RowMapper<Clazz>() {
                @Override
                public Clazz mapRow(ResultSet resultSet, int i) throws SQLException {
                    Clazz clazz = new Clazz();

                    clazz.setId(resultSet.getLong("id"));
                    clazz.setName(resultSet.getString("sName"));
                    clazz.setNumber(resultSet.getString("sNumber"));
                    clazz.setDeptNum(resultSet.getString("sDeptNum"));
                    clazz.setSpecNum(resultSet.getString("sSpecNum"));
                    clazz.setTeacherNum(resultSet.getString("sTeacherNum"));

                    return clazz;
                }
            });
        }catch (EmptyResultDataAccessException ex){
            log.debug("未获取到指定班级数据:EmptyResultDataAccessException ex");

            return null;
        }
    }

    /**
     * 获取班级列表
     * @param departNum 系部代码
     * @param specNum 专业代码
     * @param tag 标记（0：当前，1：历史）
     * @param page 页码
     * @param pageSize 每页显示条数
     * @return Page
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public Page<Clazz> getList(List<String> departNum,List<String> specNum,int tag, int page,int pageSize){
        //数据表
        String tableName = "t_class";

        //查询条件
        String where = " WHERE 1=1";

        MapSqlParameterSource param = new MapSqlParameterSource();

        //判断是否限制系部
        if(!departNum.isEmpty()){
            log.debug("限制系部代码:!departNum.isEmpty()");

            where += " AND sDeptNum IN(:deptNum)";

            param.addValue("deptNum", departNum);
        }

        //判断是否限制专业
        if(!specNum.isEmpty()){
            log.debug("限制专业数据:!specNum.isEmpty()");

            where += " AND sSpecNum IN(:specNum)";

            param.addValue("specNum", specNum);
        }

        //判断是否是获取历史数据
        if(1 == tag){
            log.debug("获取归档数据:1 == tag");

            tableName = "t_hclass";
        }

        String totalSql = "SELECT COUNT(id) COUNT FROM "+tableName+where;

        int total = namedParameterJdbcTemplate.queryForObject(totalSql,param,Integer.class);

        if(0 == total){
            log.debug("班级数据为空:0 == total");

            return new Page<>();
        }

        String dataSql = "SELECT id,sName,sNumber,sDeptNum,sSpecNum,sTeacherNum FROM "+tableName+where+getLimitStr(page,pageSize);

        List<Clazz> clazzs = namedParameterJdbcTemplate.query(dataSql, param, new RowMapper<Clazz>() {
            @Override
            public Clazz mapRow(ResultSet rs, int i) throws SQLException {
                Clazz clazz = new Clazz();

                clazz.setId(rs.getLong("id"));
                clazz.setName(rs.getString("sName"));
                clazz.setNumber(rs.getString("sNumber"));
                clazz.setDeptNum(rs.getString("sDeptNum"));
                clazz.setSpecNum(rs.getString("sSpecNum"));
                clazz.setTeacherNum(rs.getString("sTeacherNum"));

                return clazz;
            }
        });

        return new Page<>(total,pageSize,page,clazzs);
    }

    /**
     * 批量新增班级数据
     * @param classList 班级信息集合
     * @return int[]
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public int[] saveClasses(final List<Clazz> classList) {
        String sql = "INSERT INTO t_class(sName,sNumber,sDeptNum,sSpecNum,sTeacherNum) VALUES(:name,:number,:deptNum,:specNum,:teacherNum)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(classList.toArray());

        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }


    /**
     * 归档班级信息
     * @return int
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public int saveHistory(){
        String sql = "INSERT INTO t_hclass (SELECT id, sName, sNumber, sDeptNum, sSpecNum, sTeacherNum FROM t_class)";

        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource());
    }

    /**
     * 清空班级主表（保留主键）
     * @return int
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public int deleteAll(){
        String sql = "DELETE FROM t_class";

        return namedParameterJdbcTemplate.update(sql, new MapSqlParameterSource());
    }

    /**
     * 删除指定班级信息
     * @param id 编号
     * @return boolean
     *
     * @author fanruiping
     * @since 2015-05-05
     */
    public boolean deleteById(Long id){
        String sql = "DELETE FROM t_class WHERE id=:id";

        return namedParameterJdbcTemplate.update(sql,new MapSqlParameterSource().addValue("id",id)) >= 0;
    }

    /**
     * 获取教师所负责的班级编号集合
     * @param teacherNum 教师工号
     * @return List
     *
     * @author fanruiping
     * @since 2015-05-07
     */
    public List<Long> getIdsByTeacherNum(String teacherNum){
        String sql = "SELECT id FROM t_class WHERE sTeacherNum=:teacherNum";

        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("teacherNum", teacherNum);

        return namedParameterJdbcTemplate.queryForList(sql,params,Long.class);
    }
}
